﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.ServiceModel.Activation;
using NHibernate;
using DunSilverlight.Web.Comm;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using DunLibrary.Report;
using DunSilverlight.Web.Serializable;

namespace DunSilverlight.Web.ReportWcf
{
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    public class ReportService : BaseService<PhoneCallStatistic>, IReportService
    {

        public string GetPhoneCallStatisticReport(int yearPeriod, int? ImportTime,DateTime start, DateTime end, string bankNum, string orgNum)
        {
            try
            {
                using (ISession sess = SessionFactory.HbmSessionFactory.OpenSession())
                {
                    IDbConnection cnn = sess.Connection;

                    string simportTime = ImportTime == null ? "null" : ImportTime.Value.ToString();

                    string sql = string.Format("exec PhoneCallStatistic {0},{1},'{2}','{3}','{4}','{5}'",
                        yearPeriod,simportTime, start, end, bankNum, orgNum);


                    DbDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.Connection = cnn as SqlConnection;
                    da.SelectCommand.CommandText = sql;

                    DataTable table = new DataTable();
                    da.Fill(table);

                    List<PhoneCallStatistic> list = new List<PhoneCallStatistic>();

                    for (int i = 0; i < table.Rows.Count; i++)
                    {

                        PhoneCallStatistic obj = new PhoneCallStatistic();
                        obj.OperName = table.Rows[i]["OperName"].ToString();
                        obj.Bank = table.Rows[i]["Bank"].ToString();
                        int tmp = 0;
                        if (int.TryParse(table.Rows[i]["CallCount"].ToString(), out tmp))
                            obj.CallCount = tmp;
                        list.Add(obj);
                    }
                    string json = JsonSerializerHelper.EntityToJson(list);
                    return PackJsonListResult("true", json, string.Empty, list.Count);
                }
            }
            catch (Exception ex)
            {
                return this.PackJsonListResult("false", "[]", ex.Message, 0);
            }
        }



        public string GetCaseReturnStatisticReport(int yearPeriod, int? ImportTime, int? operId, string arrealDiffStart, string arrealDiffEnd,
            double? arrealAmountStart, double? arrealAmountEnd, int ?bankId, string orgNum)
        {
            try
            {
                using (ISession sess = SessionFactory.HbmSessionFactory.OpenSession())
                {
                    IDbConnection cnn = sess.Connection;

                    string simportTime = ImportTime == null ? "null" : ImportTime.Value.ToString();
                    string soperId = operId == null ? "null" : operId.Value.ToString();
                    string sarrealAmountStart = arrealAmountStart == null ? "null" : arrealAmountStart.Value.ToString();
                    string sarrealAmountEnd = arrealAmountEnd == null ? "null" : arrealAmountEnd.Value.ToString();
                     string sbankId = bankId == null ? "null" : bankId.Value.ToString();

                    string sql = string.Format("exec CaseReturnStatistic {0},{1},{2},'{3}','{4}',{5},{6},{7},'{8}'",
                        yearPeriod,simportTime, soperId, arrealDiffStart, arrealDiffEnd, sarrealAmountStart,
                        sarrealAmountEnd, sbankId, orgNum);


                    DbDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.Connection = cnn as SqlConnection;
                    da.SelectCommand.CommandText = sql;

                    DataTable table = new DataTable();
                    da.Fill(table);

                    List<CaseReturnStatistic> list = new List<CaseReturnStatistic>();

                    for (int i = 0; i < table.Rows.Count; i++)
                    {

                        CaseReturnStatistic obj = new CaseReturnStatistic();
                        obj.BankId = int.Parse(table.Rows[i]["BankId"].ToString());
                        obj.BankName = table.Rows[i]["BankName"].ToString();
                        obj.CardDelayLevel = table.Rows[i]["CardDelayLevel"].ToString();
                        {
                            int tmp;
                            if (int.TryParse(table.Rows[i]["CardCount"].ToString(), out tmp))
                                obj.CardCount = tmp;
                        }
                        {
                            double tmp;
                            if (double.TryParse(table.Rows[i]["TotalArrear"].ToString(), out tmp))
                                obj.TotalArrear = tmp;
                            if (double.TryParse(table.Rows[i]["TotalReturn"].ToString(), out tmp))
                                obj.TotalReturn = tmp;
                            if (double.TryParse(table.Rows[i]["ReturnPercent"].ToString(), out tmp))
                            {
                                obj.ReturnPercent = tmp;
                                if (obj.ReturnPercent > 100)
                                    obj.ReturnPercent = 100;
                            }
                        }
                        list.Add(obj);
                    }
                    string json = JsonSerializerHelper.EntityToJson(list);
                    return PackJsonListResult("true", json, string.Empty, list.Count);
                }
            }
            catch (Exception ex)
            {
                return this.PackJsonListResult("false", "[]", ex.Message, 0);
            }
        }


        public string GetPhoneCallQuery(int yearPeriod, int? ImportTime, double? arrealAmountStart, double? arrealAmountEnd,
            int? operId, string cardNo, string holderName, string idCard, string bankNum, int? subBankId, string orgNum)
        {
            try
            {
                using (ISession sess = SessionFactory.HbmSessionFactory.OpenSession())
                {
                    IDbConnection cnn = sess.Connection;

                    string simportTime = ImportTime == null ? "null" : ImportTime.Value.ToString();
                    string sarrealAmountStart = arrealAmountStart == null ? "null" : arrealAmountStart.Value.ToString();
                    string sarrealAmountEnd = arrealAmountEnd == null ? "null" : arrealAmountEnd.Value.ToString();
                    string soperId = operId == null ? "null" : operId.Value.ToString();
                    string ssubBankId = subBankId == null ? "null" : subBankId.Value.ToString();

                    string sql = string.Format("exec PhoneCallQuery {0},{1},{2},{3},{4},'{5}','{6}','{7}','{8}',{9},'{10}'",
                        yearPeriod,simportTime, sarrealAmountStart, sarrealAmountEnd,
                        soperId, cardNo, holderName, idCard,bankNum, ssubBankId, orgNum);


                    DbDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.Connection = cnn as SqlConnection;
                    da.SelectCommand.CommandText = sql;

                    DataTable table = new DataTable();
                    da.Fill(table);

                    List<PhoneCallQuery> list = new List<PhoneCallQuery>();

                    for (int i = 0; i < table.Rows.Count; i++)
                    {

                        PhoneCallQuery obj = new PhoneCallQuery();
                        obj.YearPeriod = int.Parse(table.Rows[i]["YearPeriod"].ToString());
                        obj.HolderName = table.Rows[i]["HolderName"].ToString();
                        obj.IdCard = table.Rows[i]["IdCard"].ToString();
                        obj.CardNo = table.Rows[i]["CardNo"].ToString();
                        obj.Currency = table.Rows[i]["Currency"].ToString();
                        {
                            double tmp;
                            if (double.TryParse(table.Rows[i]["Amount"].ToString(), out tmp))
                                obj.Amount = tmp;
                        }
                        {
                            DateTime tmp;
                            if (DateTime.TryParse(table.Rows[i]["CallTime"].ToString(), out tmp))
                                obj.CallTime = tmp;
                            if (DateTime.TryParse(table.Rows[i]["AgreeTime"].ToString(), out tmp))
                                obj.AgreeTime = tmp;
                        }
                        if (obj.AgreeTime != null)
                        {
                            double tmp;
                            if (double.TryParse(table.Rows[i]["AgreeAmount"].ToString(), out tmp))
                                obj.AgreeAmount = tmp;
                        }
                        obj.Contact = table.Rows[i]["Contact"].ToString();
                        obj.ReceiveType = table.Rows[i]["ReceiveType"].ToString();
                        obj.CallContent = table.Rows[i]["CallContent"].ToString();
                        obj.BankName = table.Rows[i]["BankName"].ToString();
                        obj.OperName = table.Rows[i]["OperName"].ToString();
                        obj.DunType = "电话催收/上门催收";
                        obj.PersonNo = obj.AreaNo = ""; //不知道意思，暂时为空
                        obj.CardDelayLevel = table.Rows[i]["CardDelayLevel"].ToString();
                        list.Add(obj);
                    }
                    string json = JsonSerializerHelper.EntityToJson(list);
                    return PackJsonListResult("true", json, string.Empty, list.Count);
                }
            }
            catch (Exception ex)
            {
                return this.PackJsonListResult("false", "[]", ex.Message, 0);
            }
        }


        public string WordPage(int yearPeriod, int? ImportTime, string arrealDiffStart, string arrealDiffEnd, 
            double? arrealAmountStart, double? arrealAmountEnd, int? operId, string cardNo, 
            string holderName, string idCard, int ?bankId, string orgNum)
        {
            try
            {
                using (ISession sess = SessionFactory.HbmSessionFactory.OpenSession())
                {
                    IDbConnection cnn = sess.Connection;

                    string simportTime = ImportTime == null ? "null" : ImportTime.Value.ToString();
                    string sarrealAmountStart = arrealAmountStart == null ? "null" : arrealAmountStart.Value.ToString();
                    string sarrealAmountEnd = arrealAmountEnd == null ? "null" : arrealAmountEnd.Value.ToString();
                    string soperId = operId == null ? "null" : operId.Value.ToString();
                    string sbankId = bankId == null ? "null" : bankId.Value.ToString();

                    string sql = string.Format("exec WordPage {0},{1},'{2}','{3}',{4},{5},{6},'{7}','{8}','{9}',{10},'{11}'",
                        yearPeriod,simportTime, arrealDiffStart,arrealDiffEnd,sarrealAmountStart,sarrealAmountEnd,
                        soperId,cardNo,holderName,idCard,sbankId,orgNum);

                    DbDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.Connection = cnn as SqlConnection;
                    da.SelectCommand.CommandText = sql;

                    DataTable table = new DataTable();
                    da.Fill(table);

                    List<WordPage> list = new List<WordPage>();
                    int tmpI;
                    double tmpD;
                    for (int i = 0; i < table.Rows.Count; i++)
                    {

                        WordPage obj = new WordPage();
                        if (int.TryParse(table.Rows[i]["ArId"].ToString(), out tmpI))
                            obj.ArId = tmpI;
                        if (int.TryParse(table.Rows[i]["CaseId"].ToString(), out tmpI))
                            obj.CaseId = tmpI;
                        obj.BankName = table.Rows[i]["BankName"].ToString();
                        if (int.TryParse(table.Rows[i]["YearPeriod"].ToString(), out tmpI))
                            obj.YearPeriod = tmpI;
                        obj.HolderName = table.Rows[i]["HolderName"].ToString();
                        obj.IdCard = table.Rows[i]["IdCard"].ToString();
                        if (int.TryParse(table.Rows[i]["CardId"].ToString(), out tmpI))
                            obj.CardId = tmpI;
                        obj.CardNo = table.Rows[i]["CardNo"].ToString();
                        obj.CardDelayLevel = table.Rows[i]["CardDelayLevel"].ToString();
                        if (double.TryParse(table.Rows[i]["TotalArrear"].ToString(), out tmpD))
                            obj.TotalArrear = tmpD;
                        if (double.TryParse(table.Rows[i]["TotalReturn"].ToString(), out tmpD))
                            obj.TotalReturn = tmpD;
                        if (double.TryParse(table.Rows[i]["TotalSub"].ToString(), out tmpD))
                            obj.TotalSub = tmpD;
                        obj.OperName = table.Rows[i]["OperName"].ToString();
                        obj.Currency = table.Rows[i]["Currency"].ToString();
                        list.Add(obj);

                    }
                    string json = JsonSerializerHelper.EntityToJson(list);
                    return PackJsonListResult("true", json, string.Empty, list.Count);
                }
            }
            catch (Exception ex)
            {
                return this.PackJsonListResult("false", "[]", ex.Message, 0);
            }

        }

        public string ExcelPage(int yearPeriod, int? ImportTime, string arrealDiffStart, string arrealDiffEnd, 
            double? arrealAmountStart, double? arrealAmountEnd, int? operId, string cardNo,
            string holderName, string idCard, int ?bankId, string orgNum)
        {
            try
            {
                using (ISession sess = SessionFactory.HbmSessionFactory.OpenSession())
                {
                    IDbConnection cnn = sess.Connection;

                    string simportTime = ImportTime == null ? "null" : ImportTime.Value.ToString();
                    string sarrealAmountStart = arrealAmountStart == null ? "null" : arrealAmountStart.Value.ToString();
                    string sarrealAmountEnd = arrealAmountEnd == null ? "null" : arrealAmountEnd.Value.ToString();
                    string soperId = operId == null ? "null" : operId.Value.ToString();
                    string sbankId = bankId == null ? "null" : bankId.Value.ToString();

                    string sql = string.Format("exec excelPage {0},{1},'{2}','{3}',{4},{5},{6},'{7}','{8}','{9}',{10},'{11}'",
                        yearPeriod,simportTime, arrealDiffStart, arrealDiffEnd, sarrealAmountStart, sarrealAmountEnd,
                        soperId, cardNo, holderName, idCard, sbankId, orgNum);

                    DbDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.Connection = cnn as SqlConnection;
                    da.SelectCommand.CommandText = sql;

                    DataTable table = new DataTable();
                    da.Fill(table);

                    List<ExcelPage> list = new List<ExcelPage>();

                    int tmpI;
                    double tmpD;
                    for (int i = 0; i < table.Rows.Count; i++)
                    {

                        ExcelPage obj = new ExcelPage();
                        if (int.TryParse(table.Rows[i]["CardId"].ToString(), out tmpI))
                            obj.CardId = tmpI;
                        if (int.TryParse(table.Rows[i]["ArId"].ToString(), out tmpI))
                            obj.ArId = tmpI;
                        if (int.TryParse(table.Rows[i]["OperId"].ToString(), out tmpI))
                            obj.OperId = tmpI;
                        obj.BankName = table.Rows[i]["BankName"].ToString();
                        if (int.TryParse(table.Rows[i]["YearPeriod"].ToString(), out tmpI))
                            obj.YearPeriod = tmpI;
                        obj.HolderName = table.Rows[i]["HolderName"].ToString();
                        obj.IdCard = table.Rows[i]["IdCard"].ToString();
                        obj.CardDelayLevel = table.Rows[i]["CardDelayLevel"].ToString();
                        if (double.TryParse(table.Rows[i]["TotalArrear"].ToString(), out tmpD))
                            obj.TotalArrear = tmpD;
                        if (double.TryParse(table.Rows[i]["TotalReturn"].ToString(), out tmpD))
                            obj.TotalReturn = tmpD;
                        if (double.TryParse(table.Rows[i]["TotalSub"].ToString(), out tmpD))
                            obj.TotalSub = tmpD;

                        string lastCallTimeStr = table.Rows[i]["LastCallTime"].ToString();
                        if (string.IsNullOrEmpty(lastCallTimeStr))
                        {
                            obj.LastCallDate = null;
                        }
                        else
                        {
                            DateTime tmp;
                            if (DateTime.TryParse(lastCallTimeStr, out tmp))
                                obj.LastCallDate = tmp;
                        }

                        string agreeTimeStr = table.Rows[i]["AgreeTime"].ToString();
                        if (string.IsNullOrEmpty(agreeTimeStr))
                        {
                            obj.PromissDate = null;
                        }
                        else
                        {
                            DateTime tmp;
                            if (DateTime.TryParse(agreeTimeStr, out tmp))
                                obj.PromissDate = tmp;
                        }

                        obj.OperName = table.Rows[i]["OperName"].ToString();
                        list.Add(obj);

                    }
                    string json = JsonSerializerHelper.EntityToJson(list);
                    return PackJsonListResult("true", json, string.Empty, list.Count);
                }
            }
            catch (Exception ex)
            {
                return this.PackJsonListResult("false", "[]", ex.Message, 0);
            }

        }


        public string DunState(int arrId)
        {
           try
            {
                using (ISession sess = SessionFactory.HbmSessionFactory.OpenSession())
                {
                    IDbConnection cnn = sess.Connection;
                    string sql = string.Format("exec dunstate {0}", arrId);

                    DbDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.Connection = cnn as SqlConnection;
                    da.SelectCommand.CommandText = sql;

                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    var table = ds.Tables[0];
                    DunState obj = new DunLibrary.Report.DunState();
                    obj.BankName = table.Rows[0]["BankName"].ToString();
                    obj.HolderName = table.Rows[0]["HolderName"].ToString();
                    obj.IdCard = table.Rows[0]["IdCard"].ToString();
                    obj.OperName = table.Rows[0]["OperName"].ToString();
                    obj.CardNo = table.Rows[0]["CardNo"].ToString();
                    obj.CardDelayLevel = table.Rows[0]["CardDelayLevel"].ToString();
                    {
                        double tmp;
                        if (double.TryParse(table.Rows[0]["AmountNotPay"].ToString(), out tmp))
                            obj.AmountNotPay = tmp;
                    }
                    obj.CompanyAddress = table.Rows[0]["CompanyAddress"].ToString();
                    obj.CompanyPostcode = table.Rows[0]["CompanyPostcode"].ToString();
                    obj.CompanyPhone = table.Rows[0]["CompanyPhone"].ToString();
                    obj.CompanyName = table.Rows[0]["CompanyName"].ToString();
                    obj.HomePostcode = table.Rows[0]["HomePostcode"].ToString();
                    obj.HomeAddress = table.Rows[0]["HomeAddress"].ToString();
                    obj.HomePhone = table.Rows[0]["HomePhone"].ToString();

                    List<DunStateDetail> list = new List<DunStateDetail>();
                    table = ds.Tables[1];
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        DunStateDetail detail = new DunStateDetail();

                        detail.PhoneNumber = table.Rows[i]["Contact"].ToString();
                        {
                            DateTime tmp;
                            if (DateTime.TryParse(table.Rows[i]["CallTime"].ToString(), out tmp))
                                detail.CallTime = tmp;
                        }
                        detail.ReceiveType = table.Rows[i]["ReceiveType"].ToString();
                        detail.CallContent = table.Rows[i]["CallContent"].ToString();

                        list.Add(detail);

                    }
                    obj.Details = list;
                    string json = JsonSerializerHelper.EntityToJson(obj);
                    return PackJsonListResult("true", json, string.Empty, list.Count);
                }
            }
            catch (Exception ex)
            {
                return this.PackJsonListResult("false", "[]", ex.Message, 0);
            }
        }


        public string GetCallCount(string cardIdListStr, string yearPeriodListStr)
        {
            using (ISession sess = SessionFactory.HbmSessionFactory.OpenSession())
            {
                IDbConnection cnn = sess.Connection;

                string[] cardIdList = cardIdListStr.Split(',');
                string[] yearPeriodList = yearPeriodListStr.Split(';');

                List<string> sqlList = new List<string>();

                for (int i = 0; i < cardIdList.Length; i++)
                {
                    sqlList.Add(string.Format(@"select COUNT(*) CallCount from Dun_CallRecord a 
                                            join Dun_Case b on a.CaseId = b.Id
                                            join Card_Holder  c on b.HolderId = c.Id
                                            join Card_CreditCard d on c.Id=d.HolderId and d.Id={0}
                                            where b.YearPeriod = {1}",
                        cardIdList[i], yearPeriodList[i]));

                }

                string sql = string.Join(" union all ", sqlList);
                DbDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.Connection = cnn as SqlConnection;
                da.SelectCommand.CommandText = sql;

                DataTable table = new DataTable();
                da.Fill(table);

                List<string> list = new List<string>();

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    list.Add(table.Rows[i]["CallCount"].ToString());

                }
                return string.Join(",", list);
            }

        }


        public string GetDunRecordList(int arrId)
        {
            try
            {
                using (ISession sess = SessionFactory.HbmSessionFactory.OpenSession())
                {
                    IDbConnection cnn = sess.Connection;


                    string sql = string.Format(@"select ar.YearPeriod,rtc.Name ReceiveType,cr.CallTime,cr.Contact,cr.CallContent,
                                            card.Number CardNumber,isnull(ar.Amount,0.0) TotalArrear,isnull(ar.TotalReturnAmount,0.0) TotalReturn,
                                            ar.CardDelayLevel,ar.Currency
                                            from Dun_ArrearRecord ar 
                                            join Card_CreditCard card on ar.Id={0} and ar.CardId = card.Id
                                            join Dun_CallRecord cr on ar.CaseId = cr.CaseId
                                            join Dun_ReceiveTypeOfCall rtc on cr.ReceiveTypeId = rtc.Id
                                            ", arrId);


                    DbDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.Connection = cnn as SqlConnection;
                    da.SelectCommand.CommandText = sql;

                    DataTable table = new DataTable();
                    da.Fill(table);

                    List<DunRecord> list = new List<DunRecord>();

                    int tmpI;
                    double tmpD;
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        DunRecord obj = new DunRecord();
                        obj.CardNum = table.Rows[i]["CardNumber"].ToString();
                        if (int.TryParse(table.Rows[i]["YearPeriod"].ToString(), out tmpI))
                            obj.YearPeriod = tmpI;
                        obj.ReceiveType = table.Rows[i]["ReceiveType"].ToString();
                        obj.Contact = table.Rows[i]["Contact"].ToString();
                        string callTimeStr = table.Rows[i]["CallTime"].ToString();
                        if (!string.IsNullOrEmpty(callTimeStr))
                        {
                            DateTime tmp;
                            if (DateTime.TryParse(callTimeStr, out tmp))
                                obj.CallTime = tmp;
                        }
                        obj.CallContent = table.Rows[i]["CallContent"].ToString();
                        obj.Currency = table.Rows[i]["Currency"].ToString();
                        if (double.TryParse(table.Rows[i]["TotalArrear"].ToString(), out tmpD))
                            obj.TotalArrear = tmpD;
                        if (double.TryParse(table.Rows[i]["TotalReturn"].ToString(), out tmpD))
                            obj.TotalReturn = tmpD;
                        obj.CardDelayLevel = table.Rows[i]["CardDelayLevel"].ToString();
                        list.Add(obj);

                    }
                    string json = JsonSerializerHelper.EntityToJson(list);
                    return PackJsonListResult("true", json, string.Empty, list.Count);
                }
            }
            catch (Exception ex)
            {
                return this.PackJsonListResult("false", "[]", ex.Message, 0);
            }
        }
    }
}
